package br.com.skytef.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpSession;

import br.com.skytef.entity.LogEntity;
import br.com.skytef.jdbc.ConnectionFactory;
import br.com.skytef.utils.utils;

public class LogDao {
	public Connection connection;

	@SuppressWarnings("static-access")
	public LogDao(){
		this.connection = new ConnectionFactory().getConnection();
	}

	public LogDao(HttpSession session){
		this.connection = utils.recoverSessionDBConnection(session);
	}

	public LogDao(Connection con){
		this.connection = con;
	}

	public Connection getConnection(){
		return connection;
	}

	/*--------------------------------------------------------------------------------------------------------
	 * Insert
	 --------------------------------------------------------------------------------------------------------*/
	public LogEntity insertLog (LogEntity logEntity) throws Exception {

		try {

			PreparedStatement stmt = connection.prepareStatement(this.getQryInsertLog());
			stmt.setDate(1,logEntity.getData());
			stmt.setString(2,logEntity.getTipoConsulta());
			stmt.setTime(3,logEntity.getHoraInicioOperacao());
			stmt.setTime(4,logEntity.getPosHora());
			stmt.setTime(5,logEntity.getHoraRequisicao());
			stmt.setTime(6,logEntity.getHoraRetorno());
			stmt.setString(7,logEntity.getAlgGrupo());
			stmt.setString(8,logEntity.getAlgLoja());
			stmt.setString(9,logEntity.getAlgUrl());
			stmt.setString(10,logEntity.getAlgCliente());
			stmt.setString(11,logEntity.getAlgTransCodigo());
			stmt.setString(12,logEntity.getAlgTransMsg());
			stmt.setString(13,logEntity.getPosSerial());
			stmt.setString(14,logEntity.getPosModelo());
			stmt.setString(15,logEntity.getPosNavsVersao());
			stmt.setString(16,logEntity.getPosOsVersao());
			stmt.setString(17,logEntity.getPosLuaVersao());

			stmt.execute();
			stmt.close();

			PreparedStatement stmt2 = connection.prepareStatement("SELECT Last_insert_id() as id FROM log;");
			ResultSet rs = stmt2.executeQuery();

			int id = 0;
			if (rs.next()) {
				id = rs.getInt("id");
				logEntity.setIdLog(id);
			}
			stmt2.execute();
			stmt2.close();
		} catch (SQLException e) {
			throw new Exception(e);
		} catch (Exception e) {
			throw new Exception(e);
		}
		return logEntity;
	}

	/*--------------------------------------------------------------------------------------------------------
	 * update
	 --------------------------------------------------------------------------------------------------------*/
	public void updateLog (LogEntity logEntity) throws Exception {

		try {

			PreparedStatement stmt = connection.prepareStatement(this.getQryUpdateLog());
			stmt.setDate(1,logEntity.getData());
			stmt.setString(2,logEntity.getTipoConsulta());
			stmt.setTime(3,logEntity.getHoraInicioOperacao());
			stmt.setTime(4,logEntity.getPosHora());
			stmt.setTime(5,logEntity.getHoraRequisicao());
			stmt.setTime(6,logEntity.getHoraRetorno());
			stmt.setString(7,logEntity.getAlgGrupo());
			stmt.setString(8,logEntity.getAlgLoja());
			stmt.setString(9,logEntity.getAlgUrl());
			stmt.setString(10,logEntity.getAlgCliente());
			stmt.setString(11,logEntity.getAlgTransCodigo());
			stmt.setString(12,logEntity.getAlgTransMsg());
			stmt.setString(13,logEntity.getPosSerial());
			stmt.setString(14,logEntity.getPosModelo());
			stmt.setString(15,logEntity.getPosNavsVersao());
			stmt.setString(16,logEntity.getPosOsVersao());
			stmt.setString(17,logEntity.getPosLuaVersao());
			stmt.setInt(18,logEntity.getIdLog());		
			

			stmt.execute();
			stmt.close();

		} catch (SQLException e) {
			throw new Exception(e);
		} catch (Exception e) {
			throw new Exception(e);
		}
	}

	/*--------------------------------------------------------------------------------------------------------
	 * Select
	 --------------------------------------------------------------------------------------------------------*/

	public  List<LogEntity> searchAll() throws SQLException{
		Connection connection = new ConnectionFactory().getConnection();
		try {
			List<LogEntity> logList = new ArrayList<LogEntity>();

			PreparedStatement stmt = connection.prepareStatement(this.getQrySearchAll());
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				LogEntity log = new LogEntity();

				log.setIdLog(rs.getInt("idlog"));
				log.setData(rs.getDate("data"));
				log.setTipoConsulta(rs.getString("tipo_cons"));
				log.setPosHora(rs.getTime("hora_pos"));
				log.setHoraInicioOperacao(rs.getTime("hora_inicio_operacao"));
				log.setHoraRequisicao(rs.getTime("hora_requisicao"));
				log.setHoraRetorno(rs.getTime("hora_retorno"));
				log.setAlgGrupo(rs.getString("alg_grupo"));
				log.setAlgLoja(rs.getString("alg_loja"));
				log.setAlgUrl(rs.getString("alg_url"));
				log.setAlgCliente(rs.getString("alg_cliente"));
				log.setAlgTransCodigo(rs.getString("alg_trans_codigo"));
				log.setAlgTransMsg(rs.getString("alg_trans_msg"));
				log.setPosSerial(rs.getString("serial_pos"));
				log.setPosModelo(rs.getString("modelo_pos"));
				log.setPosOsVersao(rs.getString("os_ver_pos"));
				log.setPosNavsVersao(rs.getString("navs_ver_pos"));
				log.setPosLuaVersao(rs.getString("lua_ver_pos"));




				logList.add(log);
			} 

			rs.close();
			stmt.close();
			connection.close();
			return logList;

		} catch (SQLException e) {
			connection.rollback();
			connection.close();
			throw new RuntimeException(e);
		}

	}
	
	
	public  List<LogEntity> searchAllByDate(String ini, String fim) throws SQLException{
		Connection connection = new ConnectionFactory().getConnection();
		try {
			List<LogEntity> logList = new ArrayList<LogEntity>();

			PreparedStatement stmt = connection.prepareStatement(this.getQrySearchAllByDate(ini, fim));
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				LogEntity log = new LogEntity();

				log.setIdLog(rs.getInt("idlog"));
				log.setData(rs.getDate("data"));
				log.setTipoConsulta(rs.getString("tipo_cons"));
				log.setPosHora(rs.getTime("hora_pos"));
				log.setHoraInicioOperacao(rs.getTime("hora_inicio_operacao"));
				log.setHoraRequisicao(rs.getTime("hora_requisicao"));
				log.setHoraRetorno(rs.getTime("hora_retorno"));
				log.setAlgGrupo(rs.getString("alg_grupo"));
				log.setAlgLoja(rs.getString("alg_loja"));
				log.setAlgUrl(rs.getString("alg_url"));
				log.setAlgCliente(rs.getString("alg_cliente"));
				log.setAlgTransCodigo(rs.getString("alg_trans_codigo"));
				log.setAlgTransMsg(rs.getString("alg_trans_msg"));
				log.setPosSerial(rs.getString("serial_pos"));
				log.setPosModelo(rs.getString("modelo_pos"));
				log.setPosOsVersao(rs.getString("os_ver_pos"));
				log.setPosNavsVersao(rs.getString("navs_ver_pos"));
				log.setPosLuaVersao(rs.getString("lua_ver_pos"));




				logList.add(log);
			} 

			rs.close();
			stmt.close();
			connection.close();
			return logList;

		} catch (SQLException e) {
			connection.rollback();
			connection.close();
			throw new RuntimeException(e);
		}

	}



	/*--------------------------------------------------------------------------------------------------------
	 * Query
	 --------------------------------------------------------------------------------------------------------*/
	private String getQryInsertLog() {

		StringBuffer sql = new StringBuffer();

		sql.append("INSERT INTO log ");
		sql.append("            (data, ");
		sql.append("             tipo_cons, ");
		sql.append("             hora_inicio_operacao, ");
		sql.append("             hora_pos, ");
		sql.append("             hora_requisicao, ");
		sql.append("             hora_retorno, ");
		sql.append("             alg_grupo, ");
		sql.append("             alg_loja, ");
		sql.append("             alg_url, ");
		sql.append("             alg_cliente, ");
		sql.append("             alg_trans_codigo, ");
		sql.append("             alg_trans_msg, ");
		sql.append("             serial_pos, ");
		sql.append("             modelo_pos, ");
		sql.append("             navs_ver_pos, ");
		sql.append("             os_ver_pos, ");
		sql.append("             lua_ver_pos) ");
		sql.append("VALUES      (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); ");

		return sql.toString();

	}

	private String getQryUpdateLog() {

		StringBuffer sql = new StringBuffer();	

		sql.append("UPDATE log ");
		sql.append("SET    data = ?, ");
		sql.append("       tipo_cons = ?, ");
		sql.append("       hora_inicio_operacao = ?, ");
		sql.append("       hora_pos = ?, ");
		sql.append("       hora_requisicao = ?, ");
		sql.append("       hora_retorno = ?, ");
		sql.append("       alg_grupo = ?, ");
		sql.append("       alg_loja = ?, ");
		sql.append("       alg_url = ?, ");
		sql.append("       alg_cliente = ?, ");
		sql.append("       alg_trans_codigo = ?, ");
		sql.append("       alg_trans_msg = ?, ");
		sql.append("       serial_pos = ?, ");
		sql.append("       modelo_pos = ?, ");
		sql.append("       navs_ver_pos = ?, ");
		sql.append("       os_ver_pos = ?, ");
		sql.append("       lua_ver_pos = ? ");
		sql.append("WHERE  idlog = ?; ");  

		return sql.toString();
	}

	private String getQrySelectLog() {

		StringBuffer sql = new StringBuffer();			
		sql.append("SELECT * FROM log WHERE  idlog = ?; ");

		return sql.toString();
	}

	private String getQrySearchAll() {
		StringBuffer sql = new StringBuffer();	
		sql.append("SELECT * FROM log ");
		sql.append("ORDER BY data DESC, hora_pos DESC ;");
		return sql.toString();
	}
	
	
	private String getQrySearchAllByDate(String dtIni, String dtFim) {
		StringBuffer sql = new StringBuffer();	
		sql.append("SELECT * FROM log WHERE data between '" + dtIni + "' and '" +dtFim + "' ORDER BY data DESC, hora_pos DESC ; " );

		return sql.toString();
	}
}
